﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient; 
using System.Collections;
using System.Web;
using System.Configuration;
using Maticsoft.DBUtility;

namespace VaxemCommon
{
    public class ExcelToSQL  
    {
        
        public int ImportSql(string excelPath, string tableName)  //导入的Excel的路径，数据库里的表名  
        {
            if (!TableExist(tableName)) //表名是否存在           
                return (int)ImportState.tableNameError;
            DataTable dt = ExcelToDataTable(excelPath);
            if (dt == null)
            {
                return (int)ImportState.excelFormatError;
            }
            ArrayList tableField = GetTableField(tableName);   //表格的列名称        
            string columnName = ""; //Excel里的列名，增加一个ID列,如果ID自动递增则不需要增加ID列，只需要columnName=“”就可以了。 
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                columnName += dt.Columns[i].ColumnName + ",";
                string currentColumn = dt.Columns[i].ToString().ToUpper(); //当前列名 
                for (int j = 0; j < tableField.Count; j++)
                {

                    if (tableField[j].ToString().ToUpper() == dt.Columns[i].ToString().ToUpper())
                        break;   //跳出本层和上一层循环，continue是跳出本层循环，如果用continue，会继续执行j++              
                    //Excel里的字段必须在Sql中都有                
                    if ((tableField[j].ToString().ToUpper() != dt.Columns[i].ToString().ToUpper()) && j == tableField.Count - 1)
                        return (int)ImportState.fieldMatchError;
                }
            }
            int m = columnName.LastIndexOf(',');

            columnName = columnName.Remove(m);  //移除最后一个逗号          

            for (int h = 0; h < dt.Rows.Count; h++)
            {
                string value = " "; //如果ID自动递增ID列不需要增加了，那么value的初始值只需要value=“”就可以了。         
                for (int k = 0; k < dt.Columns.Count; k++) //根据列名得到值   
                {
                    value += "'" + dt.Rows[h][k].ToString() + "'" + ",";
                }
                value = value.Remove(0, 1);
                int n = value.LastIndexOf(',');
                value = value.Remove(n);    //移除最后一个逗号         
                n = value.LastIndexOf("'");
                value = value.Remove(n);
                value = value.Substring(1, value.Length - 1);
                try
                {

                    string sql = "insert into " + tableName + "(" + columnName + ") values('" + value + "')";
                    DbHelperOleDb.ExecuteSqls(sql);
                  
                }
                catch (Exception err)
                {
                    string erroe = err.Message;
                    return (int)ImportState.dataTypeError;
                }
            }

            return (int)ImportState.right;
        }
        public DataTable ExcelToDataTable(string excelPath)
        //把Excel里的数据转换为DataTable，并返回DataTable      
        {
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'";
            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
            string strCom = "SELECT * FROM [Sheet1$]";
            DataTable dt;
            try
            {
                Conn.Open();
                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
                DataSet ds = new DataSet();
                myCommand.Fill(ds, "[Sheet1$]");
                Conn.Close();
                dt = ds.Tables[0];
            }
            catch (Exception err)
            {
                return null;
            }
            return dt;
        }   

        public bool TableExist(string tableName)
        //查看数据库里是否有此表名     
        {

            try
            {

                string sql = "select name from sys.tables";//获取数据库中的所有表名

                SqlDataReader sqldr = DbHelperOleDb.ExecuteReaders(sql);
                 
                while (sqldr.Read())
                {
                    if (sqldr.GetString(0).ToUpper() == tableName.ToUpper())
                        return true;
                }
            }
            catch { return false; }
            finally
            {

            } return false;
        }

        public ArrayList GetTableField(string tableName)
        //得到数据库某一个表中的所有字段       
        {
            ArrayList al = new ArrayList();
            try
            {

                string sql = "SELECT b.name FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id WHERE (a.name = '" + tableName + "')";

                SqlDataReader sqldr = DbHelperOleDb.ExecuteReaders(sql);// sqlcom.ExecuteReader();     
                while (sqldr.Read())
                {
                    al.Add(sqldr.GetString(0));
                }
            }
            finally
            {

            }
            return al; //返回的是表中的字段    
        }
        public enum ImportState
        {
            right = 1, //成功         
            tableNameError = 2,//表名不存在       
            fieldMatchError = 3,//excel里的字段和数据库表里的字段不匹配     
            dataTypeError = 4, //转换数据类型时发生错误        
            excelFormatError = 5,//Excel格式不能读取      
        }
        public void Alert(string str)
        {
            HttpContext.Current.Response.Write("<script language='javascript'>alert('" + str + "');</script>");
        } 
    }
}



